//
//    Copyright (C) 2012  OinkStudio
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.


package database;

import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.LinkedList;




/**
 * try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch(Exception e){
 * System.out.println("No se ha podido cargar el Driver JDBC-ODBC"); }
 * 
 * 
 * **/

public class DataBaseAdapter {
	private Connection conec = null;
	private String url = "";
	private String dbName = "";
	private String driver = "com.mysql.jdbc.Driver"; // com.mysql.jdbc.Driver";
	private String user = "";
	private String pass = "";
	private String database;

	private static DataBaseAdapter dataBaseAdapter;

	// private ArrayList<Object> dadesRegistre = new ArrayList<Object>();
	// private ArrayList<ArrayList<Object>> totalDades = new
	// ArrayList<ArrayList<Object>>();

	public DataBaseAdapter() {
		super();
	}

	public void setParams(String url, String dbName, String user, String pass) {
		this.url = url;
		this.dbName = dbName;
		this.user = user;
		this.pass = pass;
	}

	//Dado que solo es necesario tener un objeto de tipo DataBaseAdapter, utilizamos
	//el patron singleton
	public static DataBaseAdapter getSingleton() {
		if (dataBaseAdapter == null) {
			dataBaseAdapter = new DataBaseAdapter();
		}
		return dataBaseAdapter;
	}

	public String getDbName() {
		return dbName;
	}

	public boolean openBBDD() {
		try {
			Class.forName(driver).newInstance();
			database = url + dbName;
			
			conec = DriverManager.getConnection(database, user, pass);
			
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public void close() {
		try {
			if (!conec.isClosed()) {
				conec.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// Retorna tots els camps de una taula donada
	public ArrayList<String> getColumnsFromTable(String taula) {
		ArrayList<String> fieldNames = new ArrayList<String>();
		try {
			if (conec.isClosed()) {
				openBBDD();
			}
			Statement st = conec.createStatement();
			String query = "select column_name from information_schema.columns where table_schema = "
					+ "'papeleriadalmases' and table_name = '" + taula + "';";
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				fieldNames.add(rs.getString("column_name"));
			}
			rs.close();
			st.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fieldNames;
	}

	//Recupera los nombres de las tablas de la base de datos dada
	public LinkedList<String> getTablesFromDataBase() {
		LinkedList<String> tableNames = new LinkedList<String>();
		try {
			if (conec.isClosed()) {
				openBBDD();
			}
			Statement st = conec.createStatement();
			ResultSet rs = st
					.executeQuery("select * from information_schema.tables where table_schema ='" + dbName + "';");
			while (rs.next()) {
				tableNames.add(rs.getString("TABLE_NAME"));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tableNames;

	}

	//Metodo utilizado para ejecutar cualquier query que sea de modificacion de la base de datos
	// comandos aceptados: INSERT, UPDATE, DELETE
	public int executeInsertUpdateDelete(String query) {		
		int rows = 0;

		try {
			if (conec.isClosed()) {
				openBBDD();
			}
			Statement st = conec.createStatement();
			rows = st.executeUpdate(query);			
			st.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;
	}

	//Funcion que recupera la sentencia usada para crear una tabla, a partir del argumento pasado
	//esto lo usaremos para recuperar foraneas, tipo de datos, primary key
	public String getCreateCommand(String table) {
		String query = "show create table " + table;
		String createCommand = null;
		try {
			if (conec.isClosed()) {
				openBBDD();
			}
			Statement statement = conec.createStatement();
			ResultSet resultat = statement.executeQuery(query);
			resultat.next();
			int columnIndex = resultat.findColumn("Create Table");
			createCommand = resultat.getString(columnIndex);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return createCommand;

	}
	
	//Funcion utilizada para ejecutar cualquier query que no se pueda ejecutar
	//con executeInsertUpdateDelete, devuelve los datos en un resultSet
	public ResultSet executeQuery(String query) {
		ResultSet resultat = null;
		try {
			if (conec.isClosed()) {
				openBBDD();
			}
			Statement statement = conec.createStatement();
			resultat = statement.executeQuery(query);
			// statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultat;
	}
}
